use Horizon
go

CREATE PROCEDURE spGetPassenger @pnr int
as
	if(@pnr = 0)
		select * from passengers
	else
		select * from passengers where PNRNo = @pnr

GO

CREATE PROCEDURE spGetWaitListCount @flightNo char(6)
as
	select count(*) from passenger where FlightNo = @flightNo and status = 'W'
GO

CREATE PROCEDURE spReservation(@PNRNo int,@FlightNo CHAR(6),@FName VARCHAR(15), @LName VARCHAR(15), @Age INT, @Gender CHAR(1),@Class CHAR (1),@SeatPref CHAR(1),@MealPref VARCHAR(20),@SSR VARCHAR(100))
AS
        BEGIN
				declare @amount money
				declare @status char(1)
				set @status = 'R'

                if(@Class='F')
				begin
                        update Flight set FirstSeats=(FirstSeats-1) where FlightNo = @FlightNo
                        select @amount = firstclassfare from fares where fareid = (select fareid from flight join passenger on flight.flightno = passenger.flightno)
                        insert into dailycollection values (@PNRNo, getdate(), 'R', @amount)
				end
                if(@Class='B')
				begin
                        update Flight set BusinessSeats=BusinessSeats-1 where FlightNo=@FlightNo
                        select @amount = Businessclassfare from fares where fareid = (select fareid from flight join passenger on flight.flightno = passenger.flightno)
                        insert into dailycollection values (@PNRNo, getdate(), 'R', @amount)
				end
                if(@Class='E')
				begin
                        update Flight set EconomySeats=EconomySeats-1 where FlightNo=@FlightNo
                        select @amount = Businessclassfare from fares where fareid = (select fareid from flight join passenger on flight.flightno = passenger.flightno)
                        insert into dailycollection values (@PNRNo, getdate(), 'R', @amount)
						if((select Economyseats from Flight) < 0)
							set @status = 'O'
				end
								
				INSERT INTO Passenger 
                VALUES(@PNRNo,@FlightNo,@FName,@LName,@Age,@Gender,@Class,@SeatPref,@MealPref,@SSR,@status)
        END

GO

CREATE PROCEDURE spFindFlights(@Date datetime, @Sector CHAR(5))
AS
        BEGIN

                SELECT a.FlightNo,a.Date,a.LocalDepartureTime, a.LocalArrivalTime,a.FirstSeats,a.BusinessSeats,a.EconomySeats,b.FirstClassFare,b.BusinessClassFare,b.EconomyClassFare
                FROM Flight a  join Fare b
                ON a.FareID=b.FareID
                WHERE @Date=a.Date AND @Sector=a.Sector

        END
GO

CREATE PROCEDURE spFindFlightsBySector(@Sector CHAR(5))
AS
        BEGIN

                SELECT a.FlightNo,a.Date,a.LocalDepartureTime, a.LocalArrivalTime,a.FirstSeats,a.BusinessSeats,a.EconomySeats,b.FirstClassFare,b.BusinessClassFare,b.EconomyClassFare
                FROM Flight a  join Fare b
                ON a.FareID=b.FareID
                WHERE a.Date > getdate() AND @Sector=a.Sector

        END

GO

CREATE PROCEDURE spGetDailyReport @date datetime
as
	select d.PNRNo, Sector, Amount, TranType
	from DailyCollection d join Passenger p on d.PNRNo = p.PNRNo join Flight f on p.FlightNo = f.FlightNo
	where d.TranDate = @date

GO

CREATE PROCEDURE spGetPassengersByStatus @status char, @flightNo char(6)
as
	select *
	from passenger where FlightNo = @flightNo and status = @status

GO

CREATE PROCEDURE spCalculateRefund @pnr int
as
	declare @refund money
	select @refund = amount from DailyCollection where PNRNo = @pnr and TranType != 'C'
	
	if (select datediff(day, getdate(), Date) from Passenger p join Flight f on p.FlightNo = f.FlightNo where p.status != 'O' and p.status != 'C' and p.PNRNo = @pnr) < 1
		set @refund = @refund / 1.1

	select @refund

GO

CREATE PROCEDURE spCancelPassenger @pnr int, @refund money
as
	INSERT INTO DailyCollection values (@pnr, getdate(), 'C', @refund)
	update passenger set status = 'C' where PNRNo = @pnr

GO

CREATE PROCEDURE spAddWaitList(@PNRNo int, @FlightNo CHAR(6),@FName VARCHAR(15), @LName VARCHAR(15), @Age INT, @Gender CHAR(1),@Class CHAR (1),@SeatPref CHAR(1),@MealPref VARCHAR(20),@SSR VARCHAR(100))
AS
	INSERT INTO Passenger VALUES(@PNRNo,@FlightNo,@FName,@LName,@Age,@Gender,@Class,@SeatPref,@MealPref,@SSR,'W')
GO


GO


